# -*- coding:utf-8 -*-
__author__ = 'langjin'
__date__ = "2022-2-28"
__doc__ = "数据库连接池和redis连接池的实现"
import pymysql
from loguru import logger
from datetime import datetime,timedelta
from dbutils.pooled_db import PooledDB
from config import flaskConfig



pool = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=1000,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=0,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=1000,  # 链接池中最多闲置的链接，0和None不限制
    maxshared=1000,  # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    **flaskConfig.MySQLConfig
)


class MySQLDB:
    '''
        数据的工具类
    '''
    def __init__(self):
        self.connection = pool.connection()
        self.cursor = self.connection.cursor(pymysql.cursors.DictCursor)


    def close(self):
        """
        断开数据库连接
        """
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()


    def execute(self, sql, args=()):
        """
        执行sql语句的方法
        """
        try:
            if args:
                self.cursor.execute(sql, args)
            else:
                self.cursor.execute(sql)
            logger.info(f"sql语句执行成功:{self.cursor.mogrify(sql,args)}")
        except Exception as e:
            self.rollback()
            logger.error(f"sql语句执行错误:{e}")
            raise e


    def executemany(self, sql, args=()):
        """
        执行sql语句的方法
        """
        try:
            if args:
                self.cursor.executemany(sql, args)
            else:
                self.cursor.executemany(sql)
            logger.info(f"sql语句执行成功:{self.cursor.mogrify(sql,args)}")
        except Exception as e:
            self.rollback()
            logger.error(f"sql语句执行错误:{e}")
            raise e


    def fetchone(self,sql,args=()):
        """
        执行sql语句的方法
        example: db.fetchone('SELECT * FROM users WHERE age > %s', (20,))
        """
        self.execute(sql,args)
        result = self.cursor.fetchone() or {}
        for k,v in result.items():
            if isinstance(v,datetime):
                result[k] = v.strftime("%Y-%m-%d %H:%M:%S")
            if isinstance(v, timedelta):
                result[k] = str(v)
        return result


    def fetchall(self,sql,args=()):
        """
        执行sql语句的方法
        example: db.fetchall('SELECT * FROM users WHERE age > %s', (20,))
        """
        self.execute(sql,args)
        results = []
        for result in self.cursor.fetchall():
            for k,v in result.items():
                if isinstance(v,datetime):
                    result[k] = v.strftime("%Y-%m-%d %H:%M:%S")
                if isinstance(v, timedelta):
                    result[k] = str(v)
            results.append(result)
        return results


    def insert(self, table, data):
        """
        新增数据
        example: db.insert('users', {'name': 'Alice', 'age': 25})
        """
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = 'INSERT INTO {table}({keys}) VALUES ({values});'.format(table=table, keys=keys, values=values)
        self.execute(sql, tuple(data.values()))
 

    def update(self, table, data, query):
        """
        修改数据
        example: db.update('tb_user', {'nickName':"测试2"},{"account":"test"})
        """
        sets = ', '.join(['{} = %s'.format(k) for k in data])
        condition = 'and '.join(['{} = %s'.format(k) for k in query])
        sql = 'UPDATE {table} SET {sets} WHERE {condition};'.format(table=table, sets=sets, condition=condition)
        args = list(data.values())
        args.extend(list(query.values()))
        self.execute(sql, args)
 

    def delete(self, table, query):
        """
        删除数据
        example: db.delete('users', {"age": 25})
        """
        condition= 'and '.join(['{} = %s'.format(k) for k in query])
        sql = 'DELETE FROM {table} WHERE {condition};'.format(table=table, condition=condition)
        self.execute(sql)


    def getlastrowid(self):
        """
        获取最后一条sql语句的id
        """
        return self.cursor.lastrowid
    

    def begin(self):
        """
        开启事务
        """
        self.connection.begin()
    
    def commit(self):
        """
        提交事务
        """
        self.connection.commit()
    
    def rollback(self):
        """
        回滚事务
        """
        self.connection.rollback()
